import mysql.connector
from mysql.connector import Error

class FoodKG:
    def __init__(self, host='localhost', database='food_kg',
                 user='root', password='ahj7789t'):
        self.config = {
            'host': host,
            'database': database,
            'user': user,
            'password': password
        }
        self.connection = None

    def connect(self):# 连接数据库
        try:
            self.connection = mysql.connector.connect(**self.config)
            print("成功连接到MySQL数据库")
        except Error as e:
            print(f"连接错误: {e}")

    def disconnect(self):# 断开数据库连接
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")

    def execute_query(self, query, params=None, fetch=True):# 执行查询
        cursor = None
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute(query, params or ())

            if fetch:
                if query.strip().upper().startswith('SELECT'):
                    return cursor.fetchall()
                self.connection.commit()
                return cursor.rowcount
            return None
        except Error as e:
            print(f"查询执行错误: {e}")
            return None
        finally:
            if cursor: cursor.close()

    # 添加食材
    def add_ingredient(self, name, category=None):# 添加食材
        query = """
        INSERT INTO ingredients (name, category)
        VALUES (%s, %s)
        ON DUPLICATE KEY UPDATE category=COALESCE(%s, category)
        """
        return self.execute_query(
            query,
            (name, category, category),
            fetch=False
        )

    # 添加菜品
    def add_dish(self, name, cuisine_type=None, difficulty=None):# 添加菜品
        query = """
        INSERT INTO dishes (name, cuisine_type, difficulty)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE 
            cuisine_type=COALESCE(%s, cuisine_type),
            difficulty=COALESCE(%s, difficulty)
        """
        return self.execute_query(
            query,
            (name, cuisine_type, difficulty, cuisine_type, difficulty),
            fetch=False
        )

    # 添加食材-菜品关系
    def add_relation(self, ingredient_name, dish_name, is_main=True):# 添加食材-菜品关系
        # 确保食材和菜品存在
        self.add_ingredient(ingredient_name)
        self.add_dish(dish_name)

        # 获取ID
        ingredient_id = self.execute_query(
            "SELECT ingredient_id FROM ingredients WHERE name=%s",
            (ingredient_name,)
        )[0]['ingredient_id']

        dish_id = self.execute_query(
            "SELECT dish_id FROM dishes WHERE name=%s",
            (dish_name,)
        )[0]['dish_id']

        # 添加关系
        query = """
        INSERT INTO ingredient_dish (ingredient_id, dish_id, is_main)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE is_main=%s
        """
        return self.execute_query(
            query,
            (ingredient_id, dish_id, is_main, is_main),
            fetch=False
        )

    # 根据食材推荐菜品
    def recommend_dishes(self, ingredient_names, require_all=False):
        """
        根据输入的食材推荐菜品

        参数:
            ingredient_names: 食材名称列表
            require_all: 是否要求包含所有食材 (True) 或只需部分匹配 (False)
        """
        if not ingredient_names:
            return []

        # 获取食材ID
        placeholders = ', '.join(['%s'] * len(ingredient_names))
        query = f"""
        SELECT ingredient_id FROM ingredients 
        WHERE name IN ({placeholders})
        """
        ingredient_ids = [row['ingredient_id'] for row in
                          self.execute_query(query, ingredient_names)]

        if not ingredient_ids:
            return []

        # 构建查询
        if require_all:
            # 需要包含所有食材的菜品
            query = f"""
            SELECT d.name, COUNT(id.ingredient_id) AS match_count
            FROM dishes d
            JOIN ingredient_dish id ON d.dish_id = id.dish_id
            WHERE id.ingredient_id IN ({', '.join(['%s'] * len(ingredient_ids))})
            GROUP BY d.dish_id, d.name
            HAVING COUNT(id.ingredient_id) = %s
            ORDER BY match_count DESC
            """
            params = ingredient_ids + [len(ingredient_ids)]
        else:
            # 只需部分匹配的菜品
            query = f"""
            SELECT d.name, COUNT(id.ingredient_id) AS match_count,
                   (SELECT COUNT(*) FROM ingredient_dish 
                    WHERE dish_id = d.dish_id) AS total_ingredients,
                   COUNT(id.ingredient_id) / (SELECT COUNT(*) FROM ingredient_dish 
                                           WHERE dish_id = d.dish_id) AS match_ratio
            FROM dishes d
            JOIN ingredient_dish id ON d.dish_id = id.dish_id
            WHERE id.ingredient_id IN ({', '.join(['%s'] * len(ingredient_ids))})
            GROUP BY d.dish_id, d.name
            ORDER BY match_ratio DESC, match_count DESC
            """
            params = ingredient_ids

        results = self.execute_query(query, params)

        if require_all:
            return [{'dish': row['name'], 'match': row['match_count']} for row in results]
        else:
            return [{
                'dish': row['name'],
                'match': row['match_count'],
                'total': row['total_ingredients'],
                'ratio': f"{row['match_ratio']:.0%}"
            } for row in results]